package org.nus;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Scanner;

/**
 * Class to interact with database and return string to be displayed on the web
 * page.
 * 
 * @author Xiaohui
 * 
 */
public class DBDisplayer {
	/**
	 * Database connection parameters
	 */
	private static String db = "gpstracker";
	private static String dbLoginName = "root";
	private static String dbLoginPassword = "123456";
	private static String tablename = "upload";
	private static String userTable = "user";
	private static String url = "jdbc:mysql://localhost/" + db + "?user="
			+ dbLoginName + "&password=" + dbLoginPassword;
	Connection conn = null;
	private String dataList = "";
	private String imei = null;
	private static String submitDatesForm1 = "<form method=\"post\" action=\"submitDates.do\" name=\"submitDates\">";
	private static String submitButton = "<input type=\"submit\" value=\"Submit\" name='submitButton'>"
			+ "</form>";

	private static String exportButton = "<input type=\"button\" value=\"Export Data\" name='exportButton' onclick='export.do'>";

	public DBDisplayer() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Constructor with a given imei
	 * 
	 * @param imei
	 */
	public DBDisplayer(String imei) {
		this.imei = imei;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setImei(String imei) {
		this.imei = imei;
	}

	void readFile() throws Exception {
		String filename = "dbconn.txt"; // location: parallel with WebRoot
		Scanner sc = new Scanner(new File(filename));
		// BufferedWriter bw = new BufferedWriter(new FileWriter(filename));
		// bw.write("test string");
		// bw.close();
		System.out.println(sc.nextLine());
		sc.close();
	}

	public String getDataList() {
		try {
			dataList = listData();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataList;
	}

	private String listData() throws Exception {
		String query = "SELECT * FROM " + tablename;
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		String html = "";
		// String html = "<p>Number of records: " + "</p>\n";
		html += "<TABLE BORDER=1 >";
		// table header
		html += "<TR bgcolor=\"#c0c0c0\">";

		html += "<TH>" + "SN" + "</TH>" + "<TH>" + "imei" + "</TH>" + "<TH>"
				+ "upload time" + "</TH>" + "<TH>" + "N" + "</TH>" + "<TH>"
				+ "E" + "</TH>";
		html += "</TR>";
		// the data
		int counter = 1;
		while (rs.next()) {
			html += "<TR>";
			html += "<TD>" + counter++ + "</TD>";
			html += "<TD>" + rs.getString("imei") + "</TD>";
			html += "<TD nowrap>" + rs.getString("uploadtime") + "</TD>";
			html += "<TD>" + rs.getString("Nattr") + "</TD>";
			html += "<TD>" + rs.getString("Eattr") + "</TD>";

			html += "</TR>";
		}
		html += "</TABLE>";
		// System.out.println("=========================");
		// System.out.println(html);

		// close resources
		rs.close();
		stmt.close();
		return html;
	}

	// Overloaded method
	// Get List for one gps device, given imei
	public String getSpecificList(String imei) throws Exception {
		// System.out.println("reach here");
		String query = "SELECT * FROM " + tablename + " WHERE " + " imei='"
				+ imei + "'";

		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		String html = "<TABLE BORDER=1 >";
		// table header
		html += "<TR bgcolor=\"#c0c0c0\">";

		html += "<TH>" + "SN" + "</TH>" + "<TH>" + "imei" + "</TH>" + "<TH>"
				+ "upload time" + "</TH>" + "<TH>" + "N" + "</TH>" + "<TH>"
				+ "E" + "</TH>";
		html += "</TR>";
		// the data
		int counter = 1;
		String lat;
		String lng;
		while (rs.next()) {
			lat = rs.getString("Nattr");
			lng = rs.getString("Eattr");
			html += "<TR>";
			html += "<TD>" + counter++ + "</TD>";
			html += "<TD>" + rs.getString("imei") + "</TD>";
			html += "<TD>" + rs.getString("uploadtime") + "</TD>";
			html += "<TD>" + lat + "</TD>";
			html += "<TD>" + lng + "</TD>";
			html += "<TD>"
					+ "<input value=\"Show on Map\"type=\"button\" onclick=\"initialize("
					+ lat + "," + lng + ")\"/>" + "</TD>";
			html += "</TR>";
		}
		html += "</TABLE>";
		// System.out.println("=========================");
		// System.out.println(html);

		// close resources
		rs.close();
		stmt.close();
		return html;
	}

	public String getSubmitDatesForm() {
		StringBuilder sb = new StringBuilder(submitDatesForm1);
		sb.append("\nStart: ");
		sb.append(getSelectHTML("start"));
		sb.append("&nbsp; &nbsp;");
		sb.append("End: ");
		sb.append(getSelectHTML("end"));
		sb.append("\n");

		// data shown here; check session first

		sb.append(submitButton);

		return sb.toString();
	}

	public String getInputHTML(String name) {
		return "<input name=\"username\"/>";
	}

	private StringBuilder getSelectHTMLOne(String name, int start, int end) {
		StringBuilder sb = new StringBuilder("<SELECT NAME=\"" + name + "\">\n");
		for (int i = start; i <= end; i++) {
			if (i < 10) {
				sb.append("<OPTION VALUE='" + i + "'>0" + i + "\n");
			} else {
				sb.append("<OPTION VALUE='" + i + "'>" + i + "\n");
			}
		}
		sb.append("</SELECT>");
		return sb;
	}

	public StringBuilder getSelectHTML(String prefix) {
		StringBuilder sb = getSelectHTMLOne(prefix + "_gpsyear", 2010, 2020);
		sb.append("-\n");
		sb.append(getSelectHTMLOne(prefix + "_gpsmonth", 1, 12));
		sb.append("-\n");
		sb.append(getSelectHTMLOne(prefix + "_gpsday", 01, 31));
		sb.append(" \n\n");
		sb.append(getSelectHTMLOne(prefix + "_gpshh", 0, 24));
		sb.append(":\n");
		sb.append(getSelectHTMLOne(prefix + "_gpsmm", 0, 60));
		sb.append(":\n");
		sb.append(getSelectHTMLOne(prefix + "_gpsss", 0, 60));

		return sb;
	}

	public boolean ValidateUser(String username, String password) {
		String query = "SELECT * FROM user WHERE username='" + username
				+ "' AND password='" + password + "'";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * Given two time, take the data in the time interval
	 * 
	 * @param starttime
	 * @param endtime
	 * @return
	 */
	public String getExportDataBetweenTime(String starttime, String endtime) {
		StringBuilder sb = new StringBuilder();
		if (imei == null) {
			System.err.println("In ExportAction.getDataBetweenTime, IMEI:"
					+ imei);
		} else {
			String query = "SELECT * FROM upload WHERE imei = '" + imei
					+ "' AND uploadtime>'" + starttime + "' AND uploadtime<'"
					+ endtime + "'";
			// System.out.println("query: " + query);
			try {
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(query);

				ResultSetMetaData rsmd = rs.getMetaData();
				int numcols = rsmd.getColumnCount();
				String header = "";
				for (int i = 1; i <= numcols; i++) {
					header += rsmd.getColumnName(i) + "\t";
				}
				sb.append(header + "\n");
				while (rs.next()) {
					for (int i = 1; i <= numcols; i++) {
						sb.append(rs.getString(i) + "\t");
					}
					sb.append("\n");
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sb.toString();
	}

	public String getDisplayDataBetweenTime(String starttime, String endtime)
			throws Exception {
		String html = "";
		if (imei == null) {
			System.err
					.println("In ExportAction.getDisplayDataBetweenTime, IMEI:"
							+ imei);
		} else {
			String query = "SELECT * FROM upload WHERE imei = '" + imei
					+ "' AND uploadtime>'" + starttime + "' AND uploadtime<'"
					+ endtime + "'";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			html = "<TABLE BORDER=1 >";
			// table header
			html += "<TR bgcolor=\"#c0c0c0\">";

			html += "<TH>" + "SN" + "</TH>" + "<TH>" + "imei" + "</TH>"
					+ "<TH>" + "upload time" + "</TH>" + "<TH>" + "N" + "</TH>"
					+ "<TH>" + "E" + "</TH>";
			html += "</TR>";
			// the data
			int counter = 1;
			String lat;
			String lng;
			while (rs.next()) {
				lat = rs.getString("Nattr");
				lng = rs.getString("Eattr");
				html += "<TR>";
				html += "<TD>" + counter++ + "</TD>";
				html += "<TD>" + rs.getString("imei") + "</TD>";
				html += "<TD>" + rs.getString("uploadtime") + "</TD>";
				html += "<TD>" + lat + "</TD>";
				html += "<TD>" + lng + "</TD>";
				html += "<TD>"
						+ "<input value=\"Show on Map\"type=\"button\" onclick=\"initialize("
						+ lat + "," + lng + ")\"/>" + "</TD>";
				html += "</TR>";
			}
			html += "</TABLE>";
			rs.close();
			stmt.close();
		}

		return html;
	}

	public List<String[]> getLatLonBetweenTimes(String starttime, String endtime)
			throws Exception {
		List<String[]> list = new LinkedList<String[]>();
		String query = "SELECT Nattr, Eattr FROM upload WHERE imei = '" + imei
				+ "' AND uploadtime>'" + starttime + "' AND uploadtime<'"
				+ endtime + "'";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		while (rs.next()) {
			String[] temp = { rs.getString("Nattr"), rs.getString("Eattr") };
			list.add(temp);
		}
		rs.close();
		stmt.close();
		return list;
	}

	public String getImeiList() {
		String query = "SELECT DISTINCT imei FROM upload ORDER BY imei";
		StringBuilder sb = new StringBuilder();
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			String temp;
			while (rs.next()) {
				temp = rs.getString("imei");
				// System.out.println(temp);
				sb.append("<a href=\"clickone.jsp?imei=" + temp + "\">" + temp
						+ "</a>");
				sb.append("<br/><br/>");
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sb.toString();
	}

	public String listUsers() {
		List<String> header = new ArrayList<String>();
		header.add("User Name");
		return tabulateList(fetchUserNames(), header);
	}

	public String listUsersWithDelBtn() {
		List<String> header = new ArrayList<String>();
		header.add("User Name");
		header.add("Delete");
		return tabulateListWithDel(fetchUserNames(), header);
	}

	public void addUser(String username, String password) {
		String update = "INSERT INTO " + userTable + " "
				+ "(username, password) values " + "('" + username + "','"
				+ password + "')";
		// System.out.println(update);
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(update);
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private List<String[]> fetchUserNames() {
		String query = "SELECT DISTINCT username FROM " + userTable;
		List<String[]> names = new LinkedList<String[]>();

		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String[] temp = { rs.getString(1) };
				names.add(temp);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return names;
	}

	private String tabulateListWithDel(List<String[]> items, List<String> header) {
		
		StringBuilder html = new StringBuilder(
				"<TABLE BORDER=1 width=\"600\" align=\"center\">\n<TR bgcolor=\"#c0c0c0\">\n");
		for (String s : header) {
			html.append("<TH>" + s + "</TH>\t");
		}
		html.append("\n</TR>");
		for (String[] arr : items) {
			html.append("\n<TR>\n");
			for (int i = 0; i < arr.length; i++) {
				// add user data
				html.append("<TD>" + arr[i] + "</TD>\t");
				// add del link
				html.append("<TD>" + "<a href=\"deluser.jsp?delUsername="
						+ arr[i] + "\"> Delete</a>" + "</TD>\t");
			}
			html.append("\n</TR>\n");
		}
		html.append("</TABLE>");
		return html.toString();
	}

	/**
	 * Convert items into tables to be shown on a web page.
	 * 
	 * @param items
	 * @param header
	 * @return
	 */
	private String tabulateList(List<String[]> items, List<String> header) {
		StringBuilder html = new StringBuilder(
				"<TABLE BORDER=1 align=\"center\">\n<TR bgcolor=\"#c0c0c0\">\n");
		for (String s : header) {
			html.append("<TH>" + s + "</TH>\t");
		}
		html.append("\n</TR>");
		for (String[] arr : items) {
			html.append("\n<TR>\n");
			for (int i = 0; i < arr.length; i++) {
				html.append("<TD>" + arr[i] + "</TD>\t");
			}
			html.append("\n</TR>\n");
		}
		html.append("</TABLE>");
		return html.toString();
	}

	/**
	 * Method to delete user from database.
	 * 
	 * @param username
	 */
	public void deleteUser(String username) {
		String query = "DELETE FROM " + userTable + " WHERE username='"
				+ username + "'";
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
		DBDisplayer dd = new DBDisplayer();
		// dd.addUser("test", "test");
		System.out.println(dd.listUsersWithDelBtn());
	}
}
